﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using static Dapper.SqlMapper;

namespace Zhjt.Comm.Lib.Tools.DataBaseTools
{
    public class DapperTools
    {

        public class DapperHelper
        {
            /// <summary>
            /// 公共分页方法
            /// </summary>
            /// <returns></returns>
            public static PageResult<T> SearchOnOffset<T>(SearchModel sm)
            {
                string where = " where 1=1 ";
                #region 针对自定义查询条件处理
                if (!string.IsNullOrWhiteSpace(sm.Where))
                {
                    where += sm.Where;
                }
                //声明Dapper的参数化对象
                DynamicParameters parameters = new DynamicParameters();
                //将Sql参数添加到Dapper的sql参数
                if (sm.Paras != null && sm.Paras.Count > 0)
                {
                    foreach (var item in sm.Paras)
                    {
                        parameters.Add(item.Key, item.Value);
                    }
                }
                #endregion
                if (sm.SearchWhereItem != null && sm.SearchWhereItem.Count > 0&& sm.SearchWhereItem[0]!=null)
                {
                    List<SearchWhereItem> sItem = sm.SearchWhereItem.Where(c => c.SearchType != SearchType.Other).ToList();
                    foreach (var item in sItem)
                    {
                        string key = GetSqlParaKey(item.FieldName);
                        switch (item.SearchType)
                        {
                            case SearchType.Like:
                                where += $" and { item.FieldName} like {key}";
                                parameters.Add(key, "%" + item.Value + "%");
                                break;
                            case SearchType.Equals:
                                where += $" and { item.FieldName}= {key}";
                                parameters.Add(key, item.Value);
                                break;
                        }
                    }
                }
                //拼接分页Sql
                string sql = $" select {sm.Field} from {sm.TableName}  {where} order by" +
                    $" {sm.OrderBy} OFFSET {(sm.PageIndex - 1) * sm.PageSize} ROWS FETCH NEXT" +
                    $" {sm.PageSize} ROWS ONLY  ";
                //拼接返回总行数的Sqll
                string sqlTotal = $"  select count(0) from {sm.TableName}  {where} ";
                //如果链接没有初始化,报错
                if (sm.Conn == null) throw new Exception("请初始化数据库链接");
                //如果数据库链接没有打开,就将它打开
                if (sm.Conn.State != ConnectionState.Open) sm.Conn.Open();


                //执行sql,并返回可以返回多个数据集的Dapper对象
                GridReader gridReader = sm.Conn.QueryMultiple(sql + ";" + sqlTotal, parameters);

                //读取第一个数据集
                List<T> list = gridReader.Read<T>().ToList();
                //读取第二个数据集
                int total = gridReader.ReadFirstOrDefault<int>();

                return new PageResult<T>
                {
                    Data = list,
                    Total = total,
                    Mess = "",
                    PageIndex=sm.PageIndex,
                    PageSize=sm.PageSize
                };

            }

            public static string GetSqlParaKey(string field)
            {
                return "@" + field.Replace(".", "_");
            }
        }
        public class PageResult<T>
        {
            public int Total { get; set; }
            public List<T> Data { get; set; }
            public int Code { get; set; }
            public string Mess { get; set; }

            public int PageSize { get; set; }
            public int PageCount{
                get{
                    return (int)Math.Ceiling(Total * 1.0 / PageSize);
                }
            }

            public int PageIndex { get; set; }
        }
        public class SearchModel
        {
            public SearchModel()
            {
                PageIndex = 1;
                PageSize = 10;
            }
            /// <summary>
            /// 表名
            /// </summary>
            public string TableName { get; set; }
            /// <summary>
            /// 字段名
            /// </summary>
            public string Field { get; set; }
            /// <summary>
            /// 查询条件
            /// </summary>
            public List<SearchWhereItem> SearchWhereItem { get; set; }
            /// <summary>
            /// 查询条件
            /// </summary>
            public string Where { get; set; }
            /// <summary>
            /// 排序
            /// </summary>
            public string OrderBy { get; set; }
            /// <summary>
            /// sql参数化
            /// </summary>
            public Dictionary<string, object> Paras { get; set; }
            /// <summary>
            /// 数据库链接
            /// </summary>
            public IDbConnection Conn { get; set; }
            /// <summary>
            /// 当前页
            /// </summary>
            public int PageIndex { get; set; }
            /// <summary>
            /// 页长度
            /// </summary>
            public int PageSize { get; set; }
        }

        public class SearchWhereItem
        {
            public string FieldName { get; set; }
            public string Value { get; set; }
            public SearchType SearchType { get; set; }
        }
        public enum SearchType
        {
            //模糊搜索
            Like,
            //相等搜索
            Equals,
            //其他搜索
            Other
        }
    }
}
